<?php
/*******************************************************************************

    FinalsClub.org is a platform for students and faculty to collaborate
    and share their knowledge with the world.

    Copyright (C) 2009  Andrew Magliozzi
 
    This file is a part of FinalsClub.org.

    FinalsClub.org is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.
 
    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.
 
    You should have received a copy of the GNU General Public License
    along with this program.  If not, see <http://www.gnu.org/licenses/>.
 
    To contact FinalsClub.org, send an email to info@finalsclub.org, or
    send mail to Finals Club, c/o Veritas Tutors, 1132 Massachusetts Avenue,
    Cambridge, MA 02138.

*******************************************************************************/

class LibraryPeer {
	
	// This is a static fucntion to retrun the category choices
	public static function getCategories()
	{
		$categories = array('books'=>'book', 'articles'=>'article',
					 'law'=>'law', 'math'=>'math', 'poems'=>'poem', 
					 'plays'=>'play','other'=>'other');
		
		return $categories;
	}
	
	
	public static function	retrievePublicContent($sort_by=null, $filter=null, $title=null)
	{	
		//set search clauses
		if($filter != 'null' && $filter && $filter != 'all')
		{
			$filter_clause = '= \''.$filter.'\'';
		}
		else
		{
			$filter_clause = ' IS NOT NULL ';	
		}
		
		if($title && $title != '')
		{
			$title_clause = ' LIKE \'%'.$title.'%\'';
		}		
		else
		{
			$title_clause = ' IS NOT NULL ';	
		}
		
		//summary query
		$summary_query = '(SELECT summaries.id AS \'obj_id\', \'summary\' AS \'type\',
						 summaries.updated_at AS \'date\', summaries.category AS \'category\', 
						 summaries.title AS \'title\'
				FROM summaries		  
				JOIN groups ON
				(groups.id = summaries.group_id AND
				groups.visibility = \''.GroupPeer::EVERYONE.'\')
				WHERE  
					summaries.category '.$filter_clause.'
					AND
					summaries.title '.$title_clause.' )';
		
		//file query
		$file_query = '(SELECT files.id AS \'obj_id\', \'file\' AS \'type\', created_at AS \'date\'	, files.category AS \'category\', files.name AS \'title\'  	
					FROM files
					WHERE 
					  	(files.permission_level = '.FilePeer::NETWORK_PERMISSION.'
					  	OR
					  	files.permission_level = '.FilePeer::PUBLIC_PERMISSION.')
					  	AND
					  	files.category '.$filter_clause.'
					  	AND
					  	files.name '. $title_clause.' )';
						
		//book query
		$book_query = '(SELECT books.id AS \'obj_id\', \'book\' AS \'type\', created_at AS \'date\'	, \'book\' AS \'category\'	, books.title AS \'title\'  	
					FROM books
					WHERE 
						\'book\' '.$filter_clause.'
						AND
						books.title '.$title_clause.' )';
		
		
		
		//union , sort and execute query
		$query = $summary_query.' UNION '.$file_query.' UNION '.$book_query;
		
		//create sort by clause
		if($sort_by == 'old')
		{
			$query = $query.' ORDER BY date DESC';
		}
		elseif($sort_by == 'new')
		{
			$query = $query.' ORDER BY date ASC';
		}
		elseif($sort_by == 'title')
		{
			$query = $query.' ORDER BY title ASC';
		}
		else
		{
			$query = $query.' ORDER BY date ASC';
		}
		
		
		$con = Propel::getConnection();
		$rs = $con->query($query);
		
		return $rs->fetchAll();
	}
	
	
	public static function	retrievePublicContentByCourseId($course_id, $sort_by=null, $filter=null, $title=null)
	{	
		
		//set search clauses
		if($filter != 'null' && $filter && $filter != 'all')
		{
			$filter_clause = '= \''.$filter.'\'';
		}
		else
		{
			$filter_clause = ' IS NOT NULL ';	
		}
		
		if($title && $title != '')
		{
			$title_clause = ' LIKE \'%'.$title.'%\'';
		}		
		else
		{
			$title_clause = ' IS NOT NULL ';	
		}
		
		//summary query
		$summary_query = '(SELECT summaries.id AS \'obj_id\', \'summary\' AS \'type\',
						 summaries.updated_at AS \'date\', summaries.category AS \'category\', 
						 summaries.title AS \'title\'
				FROM summaries		  
				WHERE  
					summaries.category '.$filter_clause.'
					AND
					summaries.title '.$title_clause.'
					AND
					summaries.course_id = '.$course_id.' )';
		
		//file query
		$file_query = '(SELECT files.id AS \'obj_id\', \'file\' AS \'type\', created_at AS \'date\'	, files.category AS \'category\', files.name AS \'title\'  	
					FROM files
					WHERE 
					  	(files.permission_level = '.FilePeer::PUBLIC_PERMISSION.'
					  	OR
					  	files.permission_level = '.FilePeer::NETWORK_PERMISSION.')
					  	AND
					  	files.category '.$filter_clause.'
					  	AND
					  	files.name '. $title_clause.' 
					  	AND
					  	files.course_id = '.$course_id.' )';
							
		
		
		//union , sort and execute query
		$query = $summary_query.' UNION '.$file_query;
		
		//create sort by clause
		if($sort_by == 'old')
		{
			$query = $query.' ORDER BY date DESC';
		}
		elseif($sort_by == 'new')
		{
			$query = $query.' ORDER BY date ASC';
		}
		else
		{
			$query = $query.' ORDER BY date ASC';
		}
		
		
		$con = Propel::getConnection();
		$rs = $con->query($query);
		
		return $rs->fetchAll();
		
	}
	
	
	public static function	retrieveGroupContent($sort_by=null, $filter=null, $title=null, $group_id)
	{	
		
		//set search clauses
		if($filter != 'null' && $filter && $filter != 'all')
		{
			$filter_clause = '= \''.$filter.'\'';
		}
		else
		{
			$filter_clause = ' IS NOT NULL ';	
		}
		
		if($title && $title != '')
		{
			$title_clause = ' LIKE \'%'.$title.'%\'';
		}		
		else
		{
			$title_clause = ' IS NOT NULL ';	
		}
		
		//summary query
		$summary_query = '(SELECT summaries.id AS \'obj_id\', \'summary\' AS \'type\',
						 summaries.updated_at AS \'date\', summaries.category AS \'category\', 
						 summaries.title AS \'title\'
				FROM summaries		  
				WHERE  
					summaries.category '.$filter_clause.'
					AND
					summaries.title '.$title_clause.'
					AND
					summaries.group_id = '.$group_id.' )';
		
		//file query
		$file_query = '(SELECT files.id AS \'obj_id\', \'file\' AS \'type\', created_at AS \'date\'	, files.category AS \'category\', files.name AS \'title\'  	
					FROM files
					WHERE 
					  	files.permission_level = '.FilePeer::PUBLIC_PERMISSION.'
					  	AND
					  	files.category '.$filter_clause.'
					  	AND
					  	files.name '. $title_clause.' 
					  	AND
					  	files.group_id = '.$group_id.' )';
							
		
		
		//union , sort and execute query
		$query = $summary_query.' UNION '.$file_query;
		
		//create sort by clause
		if($sort_by == 'old')
		{
			$query = $query.' ORDER BY date DESC';
		}
		elseif($sort_by == 'new')
		{
			$query = $query.' ORDER BY date ASC';
		}
		elseif($sort_by == 'title')
		{
			$query = $query.' ORDER BY title ASC';
		}
		else
		{
			$query = $query.' ORDER BY date ASC';
		}
		
		
		$con = Propel::getConnection();
		$rs = $con->query($query);
		
		return $rs->fetchAll();	
	}
	
	
	
}